import { Router } from 'express';
import { body } from 'express-validator';
import validateFormData from '../helpers/validate_form.js';
import EthersUtils from '../helpers/ethers_utils.js';
import DB from '../models/db.js';
import * as XLSX from 'xlsx';


const router = Router();



/**
 * Route to download grades template
 * @GET /grades/template
 */
router.get('/template', async (req, res) => {
	try {
		const where = {};
		let allowedRoles = ["admin"];
		let userRole = req.userRoleName;
		if (!allowedRoles.includes(userRole)) {
			if (userRole === 'teacher') where['teacher_id'] = req.user.user_id; //filter only current records
		}
		// 获取所有选课记录
		const enrollments = await DB.Enrollments.findAll({
			include: [
				{
					model: DB.Users,
					as: 'student',
					attributes: ['name', 'personnel_id']
				},
				{
					model: DB.Courses,
					as: 'course',  // Add this line to specify the alias
					attributes: ['course_name']
				}
			],
			raw: true,
			where: where,
			order: [['course_name', 'DESC'], ['enrollment_id', 'DESC']], // 按选课ID升序排序


		});

		// 准备Excel数据
		const data = [
			// 中文标题行
			[
				'学生姓名', '学号', '选课ID', '课程名称',
				'平时成绩', '考试成绩', '作业成绩', '最终成绩',
				'教师反馈', '最后更新时间', '更新人', '教师签名', '学期'
			],
			// 英文字段名行
			[
				'student_name', 'personnel_id', 'enrollment_id', 'course_name',
				'daily_grade', 'exam_grade', 'homework_grade', 'final_grade',
				'teacher_feedback', 'last_updated', 'updated_by', 'teacher_signature', 'semester_name'
			],
			// 数据行
			...enrollments.map(item => [
				item['student.name'],
				item['student.personnel_id'],
				item.enrollment_id,
				item['course.course_name'],
				'', // daily_grade
				'', // exam_grade
				'', // homework_grade
				'', // final_grade
				'', // teacher_feedback
				'', // last_updated
				'', // updated_by
				'',// teacher_signature
				new Date().getFullYear().toString()// semester_name
			])
		];

		// 创建工作簿
		const wb = XLSX.utils.book_new();
		const ws = XLSX.utils.aoa_to_sheet(data);
		XLSX.utils.book_append_sheet(wb, ws, 'GradesTemplate');

		// 生成Excel文件
		const buffer = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });

		res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		res.setHeader('Content-Disposition', 'attachment; filename=grades_template.xlsx');
		res.setHeader('Content-Length', buffer.length); // 添加内容长度头
		res.send(buffer);
	} catch (err) {
		res.serverError(err);
	}
});


/**
 * Route to list grades records grouped by student with pagination
 * @GET /grades/index/{fieldname}/{fieldvalue}
 */
router.get(['/', '/index/:fieldname?/:fieldvalue?'], async (req, res) => {
    try {
        const query = {};
        let queryFilters = [];
        let where = {};
        let replacements = {};
        let fieldName = req.params.fieldname;
        let fieldValue = req.params.fieldvalue;

        if (fieldName) {
            queryFilters.push(DB.filterBy(fieldName, fieldValue));
        }

        // 根据角色设置不同的查询条件
        let userRole = req.userRoleName;
        if (userRole === 'teacher') {
            // 教师只能查看自己教授的学生成绩
            const enrollments = await DB.Enrollments.findAll({
                where: { teacher_id: req.user.user_id },
                attributes: ['student_id'],
                raw: true
            });
            const studentIds = enrollments.map(e => e.student_id);
            where['user_id'] = studentIds;
        } else if (userRole === 'student') {
            // 学生只能查看自己的成绩
            where['user_id'] = req.user.user_id;
        }

        // 处理搜索参数
        let search = req.query.search;
        if (search) {
            const searchConditions = [
                { name: { [DB.op.like]: `%${search}%` } },
                { personnel_id: { [DB.op.like]: `%${search}%` } }
            ];
            where[DB.op.or] = searchConditions;
        }

        // 分页参数
        let page = parseInt(req.query.page) || 1;
        let limit = parseInt(req.query.limit) || 10;
        let offset = (page - 1) * limit;

        // 获取学生总数用于分页
        const totalCount = await DB.Users.count({
            where: {
                [DB.op.and]: [
                    { user_role_id: 2 }, // 学生角色ID
                    where
                ]
            }
        });

        // 获取分页后的学生及其成绩
        const studentsWithGrades = await DB.Users.findAll({
            where: {
                [DB.op.and]: [
                    { user_role_id: 2 },
                    where
                ]
            },
            attributes: ['user_id', 'name', 'personnel_id', 'avatar_url'],
            include: [{
                model: DB.Enrollments,
                as: 'enrollments',
                attributes: ['enrollment_id'],
                include: [{
                    model: DB.Grades,
                    as: 'grades',
                    attributes: [
                        'grade_id', 'daily_grade', 'exam_grade',
                        'homework_grade', 'final_grade', 'teacher_feedback',
                        'semester_name', 'teacher_signature'
                    ],
                    include: [{
                        model: DB.Enrollments,
                        as: 'enrollment',
                        attributes: ['course_name']
                    }]
                }]
            }],
            order: [['personnel_id', 'ASC']],
            limit: limit,
            offset: offset
        });

        // 格式化返回数据
        const formattedData = studentsWithGrades.map(student => ({
            name: student.name,
            personnel_id: student.personnel_id,
            avatar_url: student.avatar_url,
            grades: student.enrollments.flatMap(enrollment => 
                enrollment.grades.map(grade => ({
                    course_name: grade.enrollment.course_name,
                    ...grade.get({ plain: true })
                }))
            )
        }));

        // 返回分页结果
        return res.ok({
            records: formattedData,
            page: page,
            limit: limit,
            totalRecords: totalCount,
            totalPages: Math.ceil(totalCount / limit)
        });
    } catch (err) {
        return res.serverError(err);
    }
});


/**
 * Route to view Grades record
 * @GET /grades/view/{recid}
 */
router.get('/view/:recid', async (req, res) => {
	try {
		const recid = req.params.recid || null;
		const query = {}
		const where = {}
		where['grade_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Grades.viewFields();
		let record = await DB.Grades.findOne(query);
		if (!record) {
			return res.notFound();
		}
		return res.ok(record);
	}
	catch (err) {
		return res.serverError(err);
	}
});


/**
 * Route to insert Grades record
 * @POST /grades/add
 */
router.post('/add/',
	[
		body('grade_id').not().isEmpty().isNumeric(),
		body('enrollment_id').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('daily_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('exam_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('homework_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('final_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('teacher_feedback').optional({ nullable: true, checkFalsy: true }),
		body('updated_by').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('teacher_signature').optional({ nullable: true, checkFalsy: true }),
	], validateFormData
	, async function (req, res) {
		try {
			let modeldata = req.getValidFormData();

			//save Grades record
			let record = await DB.Grades.create(modeldata);
			//await record.reload(); //reload the record from database
			const recid = record['grade_id'];

			return res.ok(record);
		} catch (err) {
			return res.serverError(err);
		}
	});


/**
 * Route to get  Grades record for edit
 * @GET /grades/edit/{recid}
 */
router.get('/edit/:recid', async (req, res) => {
	try {
		const recid = req.params.recid;
		const query = {};
		const where = {};
		where['grade_id'] = recid;
		query.raw = true;
		query.where = where;
		query.attributes = DB.Grades.editFields();
		let record = await DB.Grades.findOne(query);
		if (!record) {
			return res.notFound();
		}
		return res.ok(record);
	}
	catch (err) {
		return res.serverError(err);
	}
});


/**
 * Route to update  Grades record
 * @POST /grades/edit/{recid}
 */
router.post('/edit/:recid',
	[
		body('grade_id').optional({ nullable: true }).not().isEmpty().isNumeric(),
		body('enrollment_id').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('daily_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('exam_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('homework_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('final_grade').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('teacher_feedback').optional({ nullable: true, checkFalsy: true }),
		body('updated_by').optional({ nullable: true, checkFalsy: true }).isNumeric(),
		body('teacher_signature').optional({ nullable: true, checkFalsy: true }),
	], validateFormData
	, async (req, res) => {
		try {
			const recid = req.params.recid;
			let modeldata = req.getValidFormData({ includeOptionals: true });
			const query = {};
			const where = {};
			where['grade_id'] = recid;
			query.raw = true;
			query.where = where;
			query.attributes = DB.Grades.editFields();
			let record = await DB.Grades.findOne(query);
			if (!record) {
				return res.notFound();
			}
			await DB.Grades.update(modeldata, { where: where });
			return res.ok(modeldata);
		}
		catch (err) {
			return res.serverError(err);
		}
	});


/**
 * Route to delete Grades record by table primary key
 * Multi delete supported by recid separated by comma(,)
 * @GET /grades/delete/{recid}
 */
router.get('/delete/:recid', async (req, res) => {
	try {
		const recid = (req.params.recid || '').split(',');
		const query = {};
		const where = {};
		where['grade_id'] = recid;
		query.raw = true;
		query.where = where;
		let records = await DB.Grades.findAll(query);
		records.forEach(async (record) => {
			//perform action on each record before delete
		});
		await DB.Grades.destroy(query);
		return res.ok(recid);
	}
	catch (err) {
		return res.serverError(err);
	}
});

/**
 * Route to bulk import grades
 * @POST /grades/bulk-import
 */
router.post('/bulk-import', async (req, res) => {
	try {
		const data = req.body;
		await DB.Grades.bulkCreate(data, {
			updateOnDuplicate: [
				'enrollment_id', 'course_name', 'daily_grade', 'exam_grade',
				'homework_grade', 'final_grade', 'teacher_feedback',
				'last_updated', 'updated_by', 'teacher_signature',
				'semester_name'
			]
		});
		return res.ok();
	} catch (err) {
		return res.serverError(err);
	}
});

/**
 * 验证教师签名接口
 * @POST /grades/verify-signature
 */
router.post('/verify-signature', async (req, res) => {
	try {
		const {
			course_name,
			daily_grade,
			exam_grade,
			homework_grade,
			final_grade,
			semester_name,
			teacher_feedback,
			signature
		} = req.body;

		// 1. 将数据排序后生成规范化字符串
		const signData = {
			course_name,
			daily_grade,
			exam_grade,
			homework_grade,
			final_grade,
			semester_name,
			teacher_feedback
		};

		const sortedData = Object.keys(signData)
			.sort()
			.reduce((obj, key) => {
				obj[key] = signData[key];
				return obj;
			}, {});
		const messageToVerify = JSON.stringify(sortedData, null, 0);

		// 2. 验证签名并恢复教师地址
		const ethersUtils = new EthersUtils('0x0000000000000000000000000000000000000000000000000000000000000001'); // 使用虚拟私钥初始化
		const teacherAddress = ethersUtils.recoverAddressFromSignature(messageToVerify, signature);

		if (!teacherAddress) {
			return res.badRequest({
				success: false,
				message: '签名验证失败'
			});
		}

		// 3. 在数据库中查找匹配的教师
		const teacher = await DB.Users.findOne({
			where: {
				ethereum_address:teacherAddress ,
				user_role_id:{
					[DB.op.in] : [1,3,4] // 教师角色ID，例如：2 或 3
				} 
			},
			attributes: ['personnel_id', 'name']
		});

		if (!teacher) {
			return res.ok({
				success: false,
				message: '未找到匹配的教师信息'
			});
		}

		// 4. 验证签名是否确实来自该教师
		const isValid = ethersUtils.verifySignature(
			messageToVerify,
			signature,
			teacherAddress
		);

		if (!isValid) {
			return res.ok({
				success: false,
				message: '签名与教师地址不匹配'
			});
		}

		return res.ok({
			success: true,
			teacherName: teacher.name,
			teacherId: teacher.personnel_id,
			message: '签名验证成功'
		});

	} catch (err) {
		console.error('签名验证过程中出错:', err);
		return res.serverError(err);
	}
});
export default router;



